package com.gxan.dao.impl;

import com.gxan.common.LayuiRepResult;
import com.gxan.dao.SalaryManagementDao;
import com.gxan.entity.Dep;
import com.gxan.entity.DepS;
import com.gxan.entity.PayList;
import com.gxan.entity.Register;
import com.gxan.utils.db.JdbcUtils;

import java.sql.SQLException;
import java.util.List;

public class SalaryManagementDaoimpl extends JdbcUtils implements SalaryManagementDao {

    @Override
    public List<PayList> findAll(Integer offset, Integer rows) throws SQLException {

        String sql = "select * from paylist limit ?,?";
        Object[] params = {offset,rows};
        return super.queryListEntity(PayList.class,sql,params);

    }
    @Override
    public Integer countPayList() throws SQLException {

        String sql = "select count(*) from paylist ";

        return super.findAllForInt(sql);
    }

    /**
     *
     * @param dep
     * @return
     * @throws SQLException
     * 通过一级部门名分组查询PayList实体类和DepS实体类中的字段,并添加到对应的表中。
     * 返回一个Integer。
     *
     */
    @Override
    public Integer addRoll(Dep dep) throws SQLException {

        Integer result = 0;

        //查询PayList实体类中的字段，返回到PayList.Class中
        String sql_findPayLit = "SELECT sys_father_dept.`dept_father`,COUNT(sys_user.`dept_id`) AS number,SUM(sal_standard.`total`) AS sal_total_basic,SUM(sal_standard.`total`) AS dis_amount,sal_standard.`register_time` AS dis_time FROM sys_user  JOIN sys_dept ON sys_dept.`dept_id` = sys_user.`dept_id`  JOIN sal_standard ON sal_standard.`sal_standard_name` = sys_user.`role`  JOIN sys_father_dept ON sys_father_dept.`dept_id` = sys_dept.`dept_father_id` WHERE sys_father_dept.`dept_father` = ?";
        PayList payList = super.queryEntity(PayList.class,sql_findPayLit,dep.getDept_father());

        //添加到数据库paylist表中。
        String sql_paylist = "INSERT INTO `hr_res`.`paylist` (`dept_father`,`number`,`sal_total_basic`,`dis_amount`,`dis_time`) VALUES (?,?,?,?,?)";
        Object[] params1 = {payList.getDept_father(),payList.getNumber(),payList.getSal_total_basic(),payList.getDis_amount(),payList.getDis_time()};
        result = super.executeUpdate(sql_paylist,params1);

        //查询Deps实体类中的字段，返回到Deps.Class中
        String sql_findDepS = "SELECT sys_father_dept.`dept_father`,sys_dept.`dept_full_name`,COUNT(*) AS countN ,SUM(sal_standard.`total`) AS wage  FROM sys_user  JOIN sys_dept ON sys_dept.`dept_id` = sys_user.`dept_id`  JOIN sal_standard ON sal_standard.`sal_standard_name` = sys_user.`role`  JOIN sys_father_dept ON sys_father_dept.`dept_id` = sys_dept.`dept_father_id` WHERE sys_father_dept.`dept_father` = ? GROUP BY sys_dept.`dept_full_name`";
        List<DepS> depS = super.queryListEntity(DepS.class,sql_findDepS,dep.getDept_father());

        //添加到数据库deps表中。
        for(DepS deps : depS) {
            String sql_deps = "INSERT INTO `hr_res`.`deps` (`dept_father`,`dept_full_name`,`countN`,`wage`) VALUES (?,?,?,?)";
            Object[] params2 = {deps.getDept_father(),deps.getDept_full_name(),deps.getCountN(),deps.getWage()};
            result += super.executeUpdate(sql_deps,params2);
        }
        System.out.println(result);

        return result;
    }
    @Override
    public Integer delRoll(String id) throws SQLException {

        String sql = "DELETE FROM `hr_res`.`paylist` WHERE `dept_father ` = ?";

        return super.executeUpdate(sql,id);
    }
    @Override
    public Integer editRoll(PayList payList) throws SQLException {

//        String sql = "UPDATE paylist SET  sal_frequency=?, number=?,  sal_total_basic=?,  dis_amount=?,  job_title=?,  dis_time=? WHERE dept_father = ?";
//        Object[] params = {payList.getSal_frequency(),payList.getNumber(),payList.getSal_total_basic(),payList.getDis_amount(),payList.getJob_title(),payList.getDis_time(),payList.getDept_father()};

        return null;
    }

    public Integer batchdelRoll(String[] ids) throws SQLException {

        String sql1 = "DELETE FROM `hr_res`.`deps` WHERE `dept_father` in (";
        for (int i = 0; i < ids.length; i++) {
            sql1 = sql1 + "?,";
        }
        sql1 = sql1.substring(0,sql1.length()-1) + ")";
        super.executeUpdate(sql1,ids);

        String sql = "delete from paylist where dept_father in (";
        for (int i = 0; i < ids.length; i++) {
            sql = sql + "?,";
        }
        sql = sql.substring(0,sql.length()-1) + ")";

        return super.executeUpdate(sql,ids);
    }
    public List findDepartment() throws SQLException {

        String sql = "select dept_father from sys_father_dept";
        return super.findAllForList(sql);
    }

    public List<DepS> findAllDeps(Integer offset, Integer rows) throws SQLException {

        String sql = "select * from deps limit ?,?";
        Object[] params = {offset,rows};

        return super.queryListEntity(DepS.class,sql,params);
    }

    public Integer countDepsList() throws SQLException{

        String sql = "select count(*) from deps";

        return super.findAllForInt(sql);
    }

    public List<DepS> search(String depsName) throws SQLException {

        String sql = "select * from deps where dept_father = ?";



        return super.queryListEntity(DepS.class,sql,depsName);
    }
    @Override
    public List<Register> findAlregisterl(String dep) throws SQLException {

        String sql = "SELECT sys_user.`login_name`,sal_standard.`basic_salary`,sal_standard.`performance`,sal_standard.`transportation`,sal_standard.`communication`,sal_standard.`meal_subsidy`,sal_standard.`housing`,sal_standard.`business`,sal_standard.`overtime_subsidy` FROM sys_user JOIN sys_dept ON sys_dept.`dept_id` = sys_user.`dept_id` JOIN sal_standard ON sal_standard.`sal_standard_name` = sys_user.`role` WHERE sys_dept.`dept_full_name` = ?";


        return super.queryListEntity(Register.class,sql,dep);

    }
}
